package phoenics.manpads.stinger.service;

import org.apache.commons.lang3.StringUtils;
import org.assertj.core.util.Lists;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 从mysql导入表
 *
 * @author Phoenics Chow
 * on 2019年01月23日
 */
public class ImportMetaDataMysql {
    private static org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(ImportMetaDataMysql.class);
    private String driverClassName;
    private String url;
    private String username;
    private String passWord;
    private JdbcTemplate jdbcTemplate;
    public ImportMetaDataMysql(String driverClassName,String url,String username,String passWord){
        this.driverClassName=driverClassName;
        this.url=url;
        this.username=username;
        this.passWord=passWord;
        //init();
    }
    public void init() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(passWord);
        jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public boolean testLink(){
        int i=jdbcTemplate.queryForObject("select 1",Integer.class);
        return i==1;
    }
    public List<Map<String, Object>> queryList(String tableName,Integer offset,Integer limit){
        String sql="select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables ";
               sql+="where table_schema = (select database()) ";
        if(StringUtils.isNotBlank(tableName))  {
            sql+="and table_name like '"+tableName.trim()+"%'";
        }
        sql+=" order by create_time desc ";
        if(offset != null && limit != null){
            sql+=" limit "+offset+", "+limit;
        }
        //logger.info(sql);
        return jdbcTemplate.queryForList(sql);

    }
    public int queryTotal(String tableName){
        String sql="select count(*) from information_schema.tables where table_schema = (select database()) ";
        if(StringUtils.isNotBlank(tableName))  {
            sql+="and table_name like '"+tableName.trim()+"%'";
        }
        //logger.info(sql);
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    public List<Map<String, Object>> queryTable(String tableName){
        String sql="select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables ";
        sql+=" where table_schema = (select database()) and table_name = '"+tableName.trim()+"' ";
        return jdbcTemplate.queryForList(sql);
    }
    public List<Map<String, Object>> queryColumns(String tableName){
        String sql="select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns ";
        sql+="where table_name = '"+tableName.trim()+"' and table_schema = (select database()) order by ordinal_position";
        return jdbcTemplate.queryForList(sql);
    }


    public PageList<Map<String, Object>> queryPage(String tableName,int page, int pagerow){
        if(pagerow<1){
            pagerow=1;
        }
        int pages = 0;   //总页数
        int limit=0;
        int rows=this.queryTotal(tableName);
        //logger.info(rows+"");
        //判断页数,如果是页大小的整数倍就为rows/pageRow如果不是整数倍就为rows/pageRow+1
        if (rows % pagerow == 0) {
            pages = rows / pagerow;
        } else {
            pages = rows / pagerow + 1;
        }
        //查询第page页的数据sql语句
        if(page>1){
            limit =((page-1)*pagerow);
        }else{
            page=1;
        }
        List<Map<String, Object>> list=this.queryList(tableName,limit,pagerow);
        //logger.info(list.size()+"");
        //返回分页格式数据
        PageList<Map<String, Object>> pl =new PageList<>();
        pl.setPage(page);  //设置显示的当前页数
        pl.setPages(pages);  //设置总页数
        pl.setList(list);   //设置当前页数据
        pl.setTotalRows(rows);    //设置总记录数
        return pl;


    }


}
